#! /usr/bin/env python
#coding=utf-8

from uliweb import expose
from sqlalchemy import *
from uliweb.orm import *
from uliweb.utils.pyini import Ini
from sqlalchemy.sql import select
from sqlalchemy.sql import and_, or_, not_

import time, sys
sys.path.insert(0, '../apps')
#from departments.models import *
#from charts.models import *
#from charts.models import *
import datetime
from datetime import date
from get_month_quarter import *

#db = get_connection('sqlite:///../database.db', debug=False)
#调用get_month_quarter.py中get_db_connection()获取数据库联接
db = get_db_connection()
#
#meta = MetaData(db)
#db.metadata.reflect()


def run():
     #从settings.ini获取任务类型编号
     settings = Ini('../apps/settings.ini')
     
     assignment_type = dict(settings.PARA.ASSIGNMENT_TYPE)
     
     req = db.metadata.tables['assignment']
     #任务明细表数据处理,后续根据任务明细表实际表名修改
     #req_mx = db.metadata.tables['assignment_mx']
     
     #获取本月月初和月末时间
     month_first_day,month_last_day,month_view = get_month()
     
     #return  month_first_day,month_last_day
     new = select([func.count(func.distinct(func.substr(req.c.ass_id,1,func.instr(req.c.ass_id,'_')))),req.c.kfzx,req.c.type],and_(req.c.release_date>=month_first_day,req.c.release_date<=month_last_day)).group_by(req.c.kfzx,req.c.type)
     
     
     #统计续建任务中本月状态为未完成或异常终止的任务
     old1 = select([func.count(func.distinct(func.substr(req.c.ass_id,1,func.instr(req.c.ass_id,'_')))),req.c.kfzx,req.c.type],
                    and_(req.c.release_date < month_first_day,
                          req.c.stage != 'FN',
                          #~req.c.status.in_(['3','4','5'])
                          )
                   ).group_by(req.c.kfzx,req.c.type)
     
     #统计续建任务中任务状态在本月修改为完成或异常终止的任务
     old2 = select([func.count(func.distinct(func.substr(req.c.ass_id,1,func.instr(req.c.ass_id,'_')))),req.c.kfzx,req.c.type],
                     and_(req.c.release_date<month_first_day,
                          #req.c.status.in_(['3','4','5']),
                          req.c.stage == 'FN',
                          #req.c.modified_date<=month_last_day,
                          #req.c.end_date>=month_first_day,
                          req.c.modified_date>=month_first_day,                    
                          #req_mx.c.modified_date<=month_last_day,
                          #req_mx.c.modified_date>=month_first_day,
                          #req_mx.c.ass_id == req.c.ass_id,
                          #req_mx.c.status == req.c.status,
                  #        req_mx.c.modified_date == func.max(req_mx.c.modified_date)
                          )
                    ).group_by(req.c.kfzx,req.c.type)
     
     #########
     #old3 = select([req.c.kfzx,req.c.type,func.count(req.c.ass_id),func.max(req_mx.c.modified_date)],
     #                or_(
     #                    and_(req.c.release_date <= month_first_day,
     #                      ~req.c.status.in_(['3','4','5'])
     #                      ),
     #                    and_(req.c.release_date<month_first_day,                                        
     #                         req.c.status.in_(['3','4','5']),                           
     #                         req_mx.c.modified_date<=month_last_day,                    
     #                         req_mx.c.modified_date>=month_first_day,                   
     #                         req_mx.c.ass_id == req.c.ass_id,                           
     #                         req_mx.c.status == req.c.status,                           
     #                 #        req_mx.c.modified_date == func.max(req_mx.c.modified_date)
     #                         ))
     #                ).group_by(req.c.kfzx,req.c.type)
     # 
     #########
     #print 'old1',old1
     #print 'old2',old2
     #print 'old3',old3
     new_rs = new.execute()
     old1_rs = old1.execute()
     old2_rs = old2.execute()
     
     
     #定义字典型data
     data = {}
     #连接kfzx表,根据开发中心id初始化data
     req_tx_model = get_model('kfzx')
     sel_tx = select([req_tx_model.c.id])
     
     
     for x in assignment_type:
     #    print x
         data[x] ={}
         tx_dic = sel_tx.execute()
         for stages in tx_dic:
             tx, = stages
             y = 'new_num_%02d' % tx
             data[x][y] = 0
             y = 'old_num_%02d' % tx
             data[x][y] = 0
     #        print x,y,data[x][y]
         data[x]['month_date'] = month_last_day
         data[x]['month'] = month_view
         data[x]['type'] = x
     
     #data['total_num'] = 0
         
     
     #以下两个for语句完成new_rs，old_rs中新增和续建准开发需求放在一条记录中                                                            
         
     for row in new_rs:
             count,tx,in_type = row
             table_row = 'new_num_%02d' % tx
             data[in_type][table_row] = count
             data[in_type]['type'] = in_type       
     for row in old1_rs:
             count,tx,in_type = row
     #        print 'old',count
             table_row = 'old_num_%02d' % tx
     #        print in_type,table_row
     #        data[in_type][table_row] += count
     #        print 'data[in_type][table_row]',data[in_type][table_row]
             old_count = data[in_type][table_row]
     #        count += old_count
             data[in_type][table_row] += count
             data[in_type]['type'] = in_type
             data[in_type]['kfzx'] = tx
     
     for row in old2_rs:
             count,tx,in_type = row
     #        print 'old',count
             #print max_modified_date
             table_row = 'old_num_%02d' % tx
     #        data[in_type][table_row] += count
             data[in_type][table_row] += count
             data[in_type]['type'] = in_type
             data[in_type]['kfzx'] = tx
     
     
     #将本月准开发需求汇总数据赋值给total_num
     
     req_model = get_model('assignment_month_report_kfzx')
     
     #插入数据前，先删除本月已有数据，该语句可以保证该脚本每个月多次跑批
     #db.execute(req_model.delete().where(req_model.c.month_date == month_last_day)) 
     req_model.remove(req_model.c.month_date == month_last_day)
     #print data
     # 按照任务类型存储统计数据
     for t,v in data.items():
         r = req_model(**v)
         r.save()

if __name__ == '__main__':
    try:
        db.begin()
        run()
        db.commit()
        log.info("finish")
    except Exception, e:
        db.rollback()
        log.exception(e)
        log.error('error')
        raise    

    
        
    
    

